package com.newbingo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.newbingo.dao.imple.IMemberDao;
import com.newbingo.entity.Member;
import com.newbingo.reportEntity.MemberReport;
import com.newbingo.util.Pager;

public class MemberDao extends HibernateDaoSupport implements IMemberDao {
	private static final Log log = LogFactory.getLog(MemberDao.class);

	/*
	 * 会员登录验证，根据用户登录名和密码
	 */
	public Member findByLogin(String uname, String upwd) {
		log.debug("finding Member instance by login");
		try {
			Member member = null;
			String hql = "from Member as member where uname=:uName and upwd=:uPwd";
			Query cri = getSession().createQuery(hql);
			cri.setString("uName", uname);
			cri.setString("uPwd", upwd);
			List<Member> members = cri.list();
			if (members.size() > 0 && members != null) {
				member = members.get(0);
			}
			return member;
		} catch (RuntimeException re) {
			log.error("find Member instance failed", re);
			throw re;
		}
	}

	/*
	 * 保存会员
	 */
	public void save(Member member) {
		log.debug("saving new Member instance");
		try {
			getSession().save(member);
			log.debug("save succesfully");
		} catch (RuntimeException re) {
			log.error("saving new Member instance failed", re);
			throw re;
		}
	}

	/*
	 * 修改会员
	 */
	public void merger(Member member) {
		log.debug("update Member instance");
		try {
			getSession().merge(member);
		} catch (RuntimeException re) {
			log.error("update Member instance failed", re);
			throw re;
		}
	}

	/*
	 * 删除会员
	 */
	public void delete(Member member) {
		log.debug("delete Member instance");
		try {
			Member deleteInstance = findById(member.getUno());
			getSession().delete(deleteInstance);
		} catch (RuntimeException re) {
			log.error("delete Member instance failed", re);
			throw re;
		}
	}

	/*
	 * 根据会员ID查询
	 */
	public Member findById(Integer id) {
		log.info("getting Member instance with id: " + id);
		try {
			Member instance = (Member) getSession().get(
					"com.newbingo.entity.Member", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	/*
	 * 根据用户名，代理商的ID进行分页查询
	 */
	public Pager findByPager(int start, int limit, String uname, Integer agentId) {
		log.debug("finding members by Pages");
		try {
			Pager pager = new Pager();
			Criteria cri = getSession().createCriteria(Member.class);
			if (uname != null)
				cri.add(Expression.like("uname", uname));
			if (agentId != null) {
				cri.createAlias("agent", "agent");
				cri.add(Expression.eq("agent.uno", agentId));
			}
			cri.setProjection(Projections.rowCount());
			pager.setTotalSize(((Number) cri.uniqueResult()).intValue());
			cri.setProjection(null);
			cri.setResultTransformer(Criteria.ROOT_ENTITY);
			cri.setFirstResult(start);
			cri.setMaxResults(limit);
			pager.setData(cri.list());
			return pager;
		} catch (RuntimeException re) {
			log.error("finding failed");
			throw re;
		}
	}
	
	/*
	 * 连接投注记录、返利记录、代理表，查询出会员的报表信息
	 */
	public Pager findMemberReportByPager(int start,int limit,Integer agentId,String startTime){
		log.debug("find member report by pager,agentId and time");
		try{
			Pager pager=new Pager();
			//设置pager的总条数，也为代理ID下面的所有会员
			Criteria cri=getSession().createCriteria(Member.class);
			cri.createAlias("agent", "agent");
			cri.add(Expression.eq("agent.uno", agentId));
			pager.setTotalSize(cri.list().size());
			//获取JDBC连接，进行进行查询，返回报表实体集合data的pager对象
			Connection conn=this.getSession().getSessionFactory().openSession().connection();
			String report="SELECT m.uname AS uname,m.trueName AS trueName,SUM(grl.amount) AS sumRebate,SUM(bt.amount) sumAmount,SUM(bt.betResult) AS sumBetResult FROM member m "
				+"LEFT JOIN gamerebatelog grl ON grl.member_uno=m.uno "
				+"LEFT JOIN agent ag ON ag.uno=m.agent_uno "
				+"LEFT JOIN bettingitem bt ON bt.member_uno=m.uno "
				+"WHERE ag.uno=? AND bt.betTime BETWEEN ? AND NOW() "
				+"GROUP BY uname ORDER BY sumAmount DESC LIMIT ?,?";
			PreparedStatement ps=conn.prepareStatement(report);
			ps.setInt(1, agentId);
			ps.setString(2,startTime);
			ps.setInt(3, start);
			ps.setInt(4, limit);
			ResultSet rs=ps.executeQuery();
			List<MemberReport> reports=new ArrayList<MemberReport>();
			while (rs.next()){
				MemberReport rep=new MemberReport();
				rep.setSumAmount(rs.getInt("sumAmount"));
				rep.setSumBetResult(rs.getInt("sumBetResult"));
				rep.setSumRebate(rs.getInt("sumRebate"));
				rep.setTrueName(rs.getString("trueName"));
				rep.setUname(rs.getString("uname"));
				reports.add(rep);
			}
			pager.setData(reports);
			return pager;
		}catch(Exception re){
			log.error("find error");
			re.printStackTrace();
			return null;
		}
	}

}
